import pandas as pd
from datetime import datetime
from db_tools import get_mysql_connection


# 读取CSV文件
def read_csv_file(file_path):
    try:
        df = pd.read_csv(file_path, encoding="utf-8")
        print(f"成功读取CSV文件，共{len(df)}条记录")
        return df
    except Exception as e:
        print(f"读取CSV文件失败: {str(e)}")
        return None


# 插入数据到MySQL
def insert_data(conn, data):
    try:
        cursor = conn.cursor()

        # 准备SQL语句 - 根据实际表结构调整
        sql = """INSERT INTO customer_inquiries 
                (serial_number, content, ask_time, wechat_id, wechat_name, group_name, group_id, created, modified) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"""

        current_time = datetime.now()

        # 遍历数据框并插入数据
        records_inserted = 0
        for index, row in data.iterrows():
            # 确保列名与CSV文件匹配
            serial_number = row.get("序号", None)
            content = row.get("内容", None)

            # 其他字段可能在CSV中不存在，使用None或默认值
            ask_time_value = row.get("ask_time", current_time)
            wechat_id = row.get("wechat_id", "wechat_id_test")
            wechat_name = row.get("wechat_name", "wechat_name_test")
            group_name = row.get("group_name", "group_name_test")
            group_id = row.get("group_id", "group_id_test")

            values = (
                serial_number,
                content,
                ask_time_value,
                wechat_id,
                wechat_name,
                group_name,
                group_id,
                current_time,  # created
                current_time,  # modified
            )
            cursor.execute(sql, values)
            records_inserted += 1

        # 提交事务
        conn.commit()
        print(f"数据插入成功！共插入{records_inserted}条记录。")

    except Exception as e:
        print(f"数据插入失败: {str(e)}")
        conn.rollback()
    finally:
        cursor.close()


def main():
    # 文件路径
    file_path = "/data_ext/wxauto/data/测试句子.csv"

    # 读取CSV文件
    df = read_csv_file(file_path)
    if df is None:
        return

    # 打印CSV文件的列名，帮助调试
    print("CSV文件的列名:", df.columns.tolist())

    # 获取数据库连接
    conn = get_mysql_connection()
    if conn is None:
        return

    try:
        # 插入数据
        insert_data(conn, df)
    finally:
        conn.close()
        print("数据库连接已关闭")


if __name__ == "__main__":
    main()
